package model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import controller.DBmanager;


public class AuctionDAO {
	
	private	String	sqlSelectAuction	=	"SELECT * FROM AUCTIONS";
	private	String	sqlSelectAAuction	=	"SELECT * FROM AUCTIONS WHERE auctionID=?";
	private	String	sqlSelectWAuction	=	"SELECT * FROM AUCTIONS WHERE dend>? AND dend<?";	
	private String	sqlSelectOAuction	=	"SELECT * FROM AUCTIONS WHERE owner=?";
	private	String	sqlNewAuction		=	"INSERT INTO AUCTIONS(title, pic, details, owner, price, dend) VALUES(?,?,?,?,?,?)";
	private	String	sqlDeleteAuction	=	"DELETE FROM AUCTIONS WHERE auctionID=?";
	
	private	Connection			con			=	null;
	private	PreparedStatement	psSelect	=	null;
	private	PreparedStatement	psSelectA	=	null;
	private	PreparedStatement	psSelectW	=	null;
	private	PreparedStatement	psSelectO	=	null;
	private	PreparedStatement	psCreate	=	null;
	private	PreparedStatement	psDelete	=	null;
	
	public AuctionDAO(){
		DBmanager myDB = DBmanager.getInstance();
		con = myDB.getConnection();
		try{
			psSelect = con.prepareStatement(sqlSelectAuction);
			psSelectA = con.prepareStatement(sqlSelectAAuction);
			psSelectW = con.prepareStatement(sqlSelectWAuction);
			psSelectO = con.prepareStatement(sqlSelectOAuction);
			psCreate = con.prepareStatement(sqlNewAuction);
			psDelete = con.prepareStatement(sqlDeleteAuction);
		}catch(SQLException e){
			printSQLException(e);
		}
	}
	
	public ArrayList<AuctionBean> selectAuctionsFromOwner(int owner){
		ArrayList<AuctionBean> beans = new ArrayList<AuctionBean>();
		try{
			psSelectO.clearParameters();
			
			psSelectO.setInt(1, owner);
			ResultSet result = psSelectO.executeQuery();
			while(result.next()){
				AuctionBean bean = new AuctionBean();
				bean.setAll(result.getInt("auctionID"), result.getString("title"), result.getString("pic"), result.getString("details"), result.getInt("owner"), result.getDouble("price"), result.getTimestamp("dend"));
				beans.add(bean);
			}
		}catch(SQLException e){
			printSQLException(e);
		}
		return beans;
	}
	
	public AuctionBean getAuction(int id){
		try{
			psSelectA.clearParameters();
			
			psSelectA.setInt(1, id);
			ResultSet result = psSelectA.executeQuery();
			while(result.next()){
				AuctionBean bean = new AuctionBean();
				bean.setAll(result.getInt("auctionID"), result.getString("title"), result.getString("pic"), result.getString("details"), result.getInt("owner"), result.getDouble("price"), result.getTimestamp("dend"));
				return bean;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public void newAuction(String name, String pic, String details, int owner, double price, Timestamp end){
		try{
			psCreate.clearParameters();
			
			psCreate.setString(1, name);
			psCreate.setString(2, pic);
			psCreate.setString(3, details);
			psCreate.setInt(4, owner);
			psCreate.setDouble(5, price);
			psCreate.setTimestamp(6, end);
			
			psCreate.executeUpdate();
		}catch(SQLException e){
			printSQLException(e);
		}
	}
	
	public ArrayList<AuctionBean> getBeans(){
		ArrayList<AuctionBean> beans = new ArrayList<AuctionBean>();
		try {
			ResultSet result = psSelect.executeQuery();
			while(result.next()){
				AuctionBean bean = new AuctionBean();
				bean.setAll(result.getInt("auctionID"), result.getString("title"), result.getString("pic"), result.getString("details"), result.getInt("owner"), result.getDouble("price"), result.getTimestamp("dend"));
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return beans;
	}
	
	public ArrayList<AuctionBean> getAuctionsThisWeek(){
		ArrayList<AuctionBean> beans = new ArrayList<AuctionBean>();
		try{
			psSelectW.clearParameters();
			psSelectW.setTimestamp(1, new java.sql.Timestamp(new java.util.Date().getTime()));
			psSelectW.setTimestamp(2, new java.sql.Timestamp(new java.util.Date().getTime()+604800000));
			ResultSet result = psSelectW.executeQuery();
			while(result.next()){
				AuctionBean bean = new AuctionBean();
				bean.setAll(result.getInt("auctionID"), result.getString("title"), result.getString("pic"), result.getString("details"), result.getInt("owner"), result.getDouble("price"), result.getTimestamp("dend"));
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return beans;
	}
	
	public void deleteAuction(int id){
		try{
			psDelete.clearParameters();
			
			psDelete.setInt(1, id);
			
			psDelete.executeUpdate();
		}catch(SQLException e){
			printSQLException(e);
		}
	}
	
	private void printSQLException(SQLException se) {
		while(se != null) {	
			se.printStackTrace();
			
			se = se.getNextException();
		}
	}
	
}
